package com.jdbc;

import javax.sql.DataSource;
import java.sql.*;

/**
 * 使用可插拔的策略执行SQL查询，并处理 ResultSet
 * 这个类是线程安全的
 */
public class QueryRunner extends AbstractQueryRunner {

    /**
     * QueryRunner 类的构造函数
     */
    public QueryRunner() {
        super();
    }

    /**
     * 用于控制是否使用 ParameterMetaData 的 QueryRunner 的构造函数
     * pmdKnownBroken：有些数据库驱动不支持 java.sql.ParameterMetaData#getParameterType(int)
     * 如果 pmdKnownBroken设置为 true, 我们不去尝试; 如果设置为 false, 我们去尝试,如果测试不能使用，我们就不再去使用它
     */
    public QueryRunner(boolean pmdKnownBroken) {
        super(pmdKnownBroken);
    }

    /**
     * 使用数据源 DataSource 的 QueryRunner 的构造函数
     * 不带有 Connection 参数的方法将会从 DataSource 中获取
     */
    public QueryRunner(DataSource ds) {
        super(ds);
    }

    /**
     * QueryRunner 的构造函数
     */
    public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
        super(ds, pmdKnownBroken);
    }

    /**
     * 批处理（一批INSERT、UPDATE、DELETE操作）
     * conn：用于执行 SQL 操作的 Connection 对象
     * Connection 由调用者负责关闭
     * sql：SQL语句
     * params：一组查询替换参数，该数组中的每一行都是一组批量替换值
     * 返回每个语句更新的行数组成的int数组
     * 数据库访问出错则抛出 SQLException 异常
     */
    public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
        return this.batch(conn, false, sql, params);
    }

    /**
     * 批处理操作
     * Connection 从 构造函数中的 DataSource 参数中获取
     * 该 Connection 必须处于自动提交模式，否则更新将不会被保存
     */
    public int[] batch(String sql, Object[][] params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.batch(conn, true, sql, params);
    }

    /**
     * 同上
     */
    private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        if (params == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
        }

        PreparedStatement stmt = null;
        int[] rows = null;
        try {
            stmt = this.prepareStatement(conn, sql);

            for (int i = 0; i < params.length; i++) {
                this.fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            rows = stmt.executeBatch();

        } catch (SQLException e) {
            this.rethrow(e, sql, (Object[])params);
        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return rows;
    }

    /**
     * 执行一个带有替换参数的SQL SELECT查询
     * 调用者负责关闭连接
     * T：处理器返回对象的类型
     * conn：执行查询的 Connection 对象
     * sql：执行的查询语句
     * rsh：将ResultSet 转换为其他对象的处理器
     * params：可变参数列表，用于替换sql语句中的占位符
     * 由 ResultSetHandler 负责返回相应对象
     * 数据库访问出错则抛出 SQLException 异常
     */
    public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return this.<T>query(conn, false, sql, rsh, params);
    }

    /**
     * 同上，只是不带有可变参数列表
     */
    public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
        return this.<T>query(conn, false, sql, rsh, (Object[]) null);
    }

    /**
     * 同上，只是不带有 Connection 参数，它会从构造函数的 DataSource 参数中获取
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.<T>query(conn, true, sql, rsh, params);
    }

    /**
     * 同上
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.<T>query(conn, true, sql, rsh, (Object[]) null);
    }

    /**
     * 在检查参数后调用查询，以确保没有任何参数值为空。
     * closeConn：True：该方法替我们关闭；false：调用者负责处理
     */
    private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
            throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        if (rsh == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null ResultSetHandler");
        }

        PreparedStatement stmt = null;
        ResultSet rs = null;
        T result = null;

        try {
            //获得 PreparedStatement 对象
            stmt = this.prepareStatement(conn, sql);
            //填充参数
            this.fillStatement(stmt, params);
            //获得ResultSet
            rs = this.wrap(stmt.executeQuery());
            //将 ResultSet 转换为指定对象
            result = rsh.handle(rs);

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        }finally {
            try {
                close(rs);
            } finally {
                close(stmt);
                if (closeConn) {
                    close(conn);
                }
            }
        }

        return result;
    }

    /**
     * 执行 SQL INSERT, UPDATE, 或者 DELETE 操作
     * 返回更新的行数
     */
    public int update(Connection conn, String sql) throws SQLException {
        return this.update(conn, false, sql, (Object[]) null);
    }

    /**
     * 同上，带有一个参数
     */
    public int update(Connection conn, String sql, Object param) throws SQLException {
        return this.update(conn, false, sql, new Object[]{param});
    }

    /**
     * 同上，带有可变参数
     */
    public int update(Connection conn, String sql, Object... params) throws SQLException {
        return update(conn, false, sql, params);
    }

    /**
     * 同上
     */
    public int update(String sql) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, (Object[]) null);
    }

    /**
     * 同上
     */
    public int update(String sql, Object param) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, new Object[]{param});
    }

    /**
     * 同上
     */
    public int update(String sql, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, params);
    }

    /**
     * 同上
     */
    private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        PreparedStatement stmt = null;
        int rows = 0;

        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rows = stmt.executeUpdate();

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return rows;
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
        return insert(this.prepareConnection(), true, sql, rsh, (Object[]) null);
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return insert(this.prepareConnection(), true, sql, rsh, params);
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
        return insert(conn, false, sql, rsh, (Object[]) null);
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return insert(conn, false, sql, rsh, params);
    }

    /**
     * 执行给定的SQL插入语句
     */
    private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
            throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        if (rsh == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null ResultSetHandler");
        }

        PreparedStatement stmt = null;
        T generatedKeys = null;

        try {
            stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            this.fillStatement(stmt, params);
            stmt.executeUpdate();
            //获取自增主键值
            ResultSet resultSet = stmt.getGeneratedKeys();
            generatedKeys = rsh.handle(resultSet);
        } catch (SQLException e) {
            this.rethrow(e, sql, params);
        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return generatedKeys;
    }

    /**
     * 执行给定的多个插入SQL语句
     */
    public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
        return insertBatch(this.prepareConnection(), true, sql, rsh, params);
    }

    /**
     * 执行给定的多个插入SQL语句
     */
    public <T> T insertBatch(Connection conn, String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
        return insertBatch(conn, false, sql, rsh, params);
    }

    /**
     * 执行给定的多个插入SQL语句
     */
    private <T> T insertBatch(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object[][] params)
            throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        if (params == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
        }

        PreparedStatement stmt = null;
        T generatedKeys = null;
        try {
            stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS);

            for (int i = 0; i < params.length; i++) {
                this.fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            stmt.executeBatch();
            ResultSet rs = stmt.getGeneratedKeys();
            generatedKeys = rsh.handle(rs);

        } catch (SQLException e) {
            this.rethrow(e, sql, (Object[])params);
        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return generatedKeys;
    }
}